package com.shop.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.shop.dao.Basedao;
import com.shop.entity.SHOP_CATEGORY;
import com.shop.entity.SHOP_USER;
/**
 * 获取所有分类
 * @author 23565
 *
 */
public class SHOP_CATEGORYDao {
	public static ArrayList<SHOP_CATEGORY> selectAll() {//int cpage, int count
		ArrayList<SHOP_CATEGORY> list=new ArrayList<SHOP_CATEGORY>();
		ResultSet rs = null;//声明结果集
   	  //获取连接对象
		Connection conn = Basedao.getconn();
		PreparedStatement ps = null;
		try {
			String sql = "select * from shop_category ";
			ps = conn.prepareStatement(sql);

			rs=ps.executeQuery();
  
			while(rs.next()) {
				SHOP_CATEGORY cate = new SHOP_CATEGORY(
						rs.getInt("CATE_ID"),
						rs.getString("CATE_NAME"),
						rs.getInt("CATE_PARENT_ID")
						);
				list.add(cate);
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Basedao.closeall(rs, ps, conn);
		}
   	  return list;
     }
  
	 /**
	  * 添加分类
	  * @param cate
	  * @return
	  */
     
	
	  public static int insert(SHOP_CATEGORY cate) {
		  
    	  String sql = "insert into shop_category values(null,?, ?)"; 
    	  Object[] params= {
    			  cate.getCATE_NAME(),
    			  cate.getCATE_PARENT_ID()
    	  };
		return Basedao.exectuIUD(sql, params);
      }
	
	  /**
	   * 
	   * @param id
	   * @return
	   */
	  
	  
	  public static SHOP_CATEGORY selectById(int id) {
		  SHOP_CATEGORY cate = null;
		  
		  ResultSet rs = null;//声明结果集
		  //获取连接对象
		  Connection conn = Basedao.getconn();
		  
		  PreparedStatement ps = null;
		  
		  try {
			  String sql = "select * from shop_category where CATE_ID=?";
			  ps = conn.prepareStatement(sql);
			  ps.setInt(1, id);
			  
			  rs=ps.executeQuery();
			  while(rs.next()) {
				  cate = new SHOP_CATEGORY(
						  rs.getInt("CATE_ID"),
						  rs.getString("CATE_NAME"),
						  rs.getInt("CATE_PARENT_ID")
						  );
				  }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				Basedao.closeall(rs, ps, conn);
			}
	    	  return cate;
	      }
	  
	  /**
	   * 
	   * @param u
	   * @return
	   */
	  public static int update(SHOP_CATEGORY cate) {
    	  String sql = "update shop_category set CATE_NAME=?, CATE_PARENT_ID=? where CATE_ID = ? "; 
    	  Object[] params= {
    			  cate.getCATE_NAME(),
    			  cate.getCATE_PARENT_ID(),
    			  cate.getCATE_ID()
    			  
    	  };
		return Basedao.exectuIUD(sql, params);
      }
      
	  
	  public static int del(int id) {
		  String sql = "delete from shop_category where CATE_ID=?";
		  Object[] params = {id};
		 return  Basedao.exectuIUD(sql, params);
	  }  
	  
	  /**
	   * 查询分类，子分类和父级分类
	   * @param flag flag="father" flag="child"
	   * @return
	   */
	  public static ArrayList<SHOP_CATEGORY> selectCat(String flag) {
		  ArrayList<SHOP_CATEGORY> list=new ArrayList<SHOP_CATEGORY>();
		  ResultSet rs = null;//声明结果集
		  //获取连接对象
		  Connection conn = Basedao.getconn();
		  PreparedStatement ps = null;
		   	   
		  try {
			  String sql = null;
			  if(flag!=null && flag.equals("father")){
				  sql = "select * from shop_category where CATE_PARENT_ID=0";
				  }else{
					  sql = "select * from shop_category where CATE_PARENT_ID!=0";
					  }
			  ps = conn.prepareStatement(sql);
			  rs = ps.executeQuery();
			  
			  while(rs.next()) {
				  SHOP_CATEGORY cate = new SHOP_CATEGORY(
						  rs.getInt("CATE_ID"),
						  rs.getString("CATE_NAME"),
						  rs.getInt("CATE_PARENT_ID")
						  );
				  list.add(cate);
				  }
			  } catch (SQLException e) {
				  // TODO Auto-generated catch block
				  e.printStackTrace();
				  } finally {
					  Basedao.closeall(rs, ps, conn);
					  }
		  
		  return list;
		  }
	  }